<?php

namespace App\Model;

use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;

class KovStatic extends BaseModel
{
    protected $table = 'kov_statistics'; //表
    protected $userTable = 'user'; //用户表
    protected $frOrderTable = 'finance_recharge_order'; //充值表
    protected $diamondStreamTable = 'finance_diamond_stream'; //流水表
    protected $userLoginLogTable = 'user_login_log'; //用户登录记录
    protected $excelOutTable = 'all_execel_out';//数据导出表
    protected $giftStream = 'gift_stream';//礼物流水

    protected $connection = 'api';

    public function getStartTimestampAttribute($value){
        if(!$value) return 0;
        return date('Y-m-d H:i:s',$value);
    }

    public function getEndTimestampAttribute($value){
        if(!$value) return 0;
        return date('Y-m-d H:i:s',$value);
    }

    //添加
    public function createData($data)
    {
        return curl_request(getKkaudioApiUrl() . '/inner/kov/create', $data, 'json', 'POST');
    }


    /**
     * 获取kov列表
     * @author 陈凯强
     */
    public function getList($page=1,$limit=25){
        $data = DB::connection($this->connection)->table($this->table)->paginate($limit,['*'],'page',$page);
        $data = $this->objToArray($data);
        return $data;
    }

    /**
     * 获取excel上传数据
     * @param int $page
     * @param int $limit
     */
    public function getUserSeach($userids=[])
    {
        $inserts = [];
        $updatas = [];
        set_time_limit(0);
        foreach($userids as $k=>$uuid) {
            //获取用户信息
            $userInfo = DB::connection($this->connection)->table($this->userTable)
                ->where('uuid', $uuid)
                ->select('uuid', 'user_id', 'create_timestamp')
                ->first();
            $userInfo = $this->objToArray($userInfo);
            $userId = empty($userInfo['user_id']) ? 0 : $userInfo['user_id'];
            //注册时间
            $register = $userInfo['create_timestamp'];
            //注册当日
            $registerDaySt = strtotime(date('Y-m-d 00:00:00', $userInfo['create_timestamp']));
            $registerDayEd = strtotime(date('Y-m-d 23:59:59', $userInfo['create_timestamp']));
            //注册三日后
            $registerDayEd3 = $registerDayEd + 86400 * 3;
            //注册七日后
            $registerDayEd7 = $registerDayEd + 86400 * 7;
        
            //总充值金额钻
            $rechargeAll = DB::connection($this->connection)->table($this->frOrderTable)
                ->where('user_id', $userId)
                ->where('status', 1)
                ->select(DB::raw('sum(diamond_amount) as num'))
                ->first();
            $rechargeAll = $this->objToArray($rechargeAll);
            //总消费钻石
            $diamondAll = DB::connection($this->connection)->table($this->diamondStreamTable)
                ->where('user_id', $userId)
                ->where('diamond_value', '<', 0)
                ->select(DB::raw('sum(diamond_value) as num'))
                ->first();
            $diamondAll = $this->objToArray($diamondAll);

            //注册当日充值钻石
            $recharge1 = DB::connection($this->connection)->table($this->frOrderTable)
                ->where('user_id', $userId)
                ->where('status', 1)
                ->where('pay_timestamp', '>=', $registerDaySt)
                ->where('pay_timestamp', '<=', $registerDayEd)
                ->select(DB::raw('sum(diamond_amount) as num'))
                ->first();
            $recharge1 = $this->objToArray($recharge1);

            //注册当日消费钻石
            $diamond1 = DB::connection($this->connection)->table($this->diamondStreamTable)
                ->where('user_id', $userId)
                ->where('diamond_value', '<', 0)
                ->where('change_timestamp', '>=', $registerDaySt)
                ->where('change_timestamp', '<=', $registerDayEd)
                ->select(DB::raw('sum(diamond_value) as num'))
                ->first();
            $diamond1 = $this->objToArray($diamond1);

            //注册后3日登陆次数
            $login3 = DB::connection($this->connection)->table($this->userLoginLogTable)
                ->where('user_id', $userId)
                ->where('time', '>=', $registerDaySt)
                ->where('time', '<=', $registerDayEd3)
                ->select(DB::raw('count(1) as num'))
                ->first();
            $login3 = $this->objToArray($login3);

            //3日充值钻石
            $recharge3 = DB::connection($this->connection)->table($this->frOrderTable)
                ->where('user_id', $userId)
                ->where('status', 1)
                ->where('pay_timestamp', '>=', $registerDaySt)
                ->where('pay_timestamp', '<=', $registerDayEd3)
                ->select(DB::raw('sum(diamond_amount) as num'))
                ->first();
            $recharge3 = $this->objToArray($recharge3);

            //3日消费钻石
            $diamond3 = DB::connection($this->connection)->table($this->diamondStreamTable)
                ->where('user_id', $userId)
                ->where('diamond_value', '<', 0)
                ->where('change_timestamp', '>=', $registerDaySt)
                ->where('change_timestamp', '<=', $registerDayEd3)
                ->select(DB::raw('sum(diamond_value) as num'))
                ->first();
            $diamond3 = $this->objToArray($diamond3);

            //7日登陆次数
            $login7 = DB::connection($this->connection)->table($this->userLoginLogTable)
                ->where('user_id', $userId)
                ->where('time', '>=', $registerDaySt)
                ->where('time', '<=', $registerDayEd7)
                ->select(DB::raw('count(1) as num'))
                ->first();
            $login7 = $this->objToArray($login7);

            //7日充值钻石
            $recharge7 = DB::connection($this->connection)->table($this->frOrderTable)
                ->where('user_id', $userId)
                ->where('status', 1)
                ->where('pay_timestamp', '>=', $registerDaySt)
                ->where('pay_timestamp', '<=', $registerDayEd7)
                ->select(DB::raw('sum(diamond_amount) as num'))
                ->first();
            $recharge7 = $this->objToArray($recharge7);

            //7日消费钻石
            $diamond7 = DB::connection($this->connection)->table($this->diamondStreamTable)
                ->where('user_id', $userId)
                ->where('diamond_value', '<', 0)
                ->where('change_timestamp', '>=', $registerDaySt)
                ->where('change_timestamp', '<=', $registerDayEd7)
                ->select(DB::raw('sum(diamond_value) as num'))
                ->first();
            $diamond7 = $this->objToArray($diamond7);
        
            $data = [
                'id' => $k+1,
                'uuid' => $uuid,
                'userId' => $userId,
                'registerDay' => date("Y-m-d H:i:s", $register),
                'rechargeAll' => empty($rechargeAll['num']) ? 0 : $rechargeAll['num'],
                'diamondAll' => empty($diamondAll['num']) ? 0 : abs($diamondAll['num']),
                'recharge1' => empty($recharge1['num']) ? 0 : $recharge1['num'],
                'diamond1' => empty($diamond1['num']) ? 0 : abs($diamond1['num']),
                'login3' => empty($login3['num']) ? 0 : $login3['num'],
                'recharge3' => empty($recharge3['num']) ? 0 : $recharge3['num'],
                'diamond3' => empty($diamond3['num']) ? 0 : abs($diamond3['num']),
                'login7' => empty($login7['num']) ? 0 : $login7['num'],
                'recharge7' => empty($recharge7['num']) ? 0 : $recharge7['num'],
                'diamond7' => empty($diamond7['num']) ? 0 : abs($diamond7['num']),
            ];
            $lists[] = $data;
        
        }
        return $lists;
    }

    //创建生成脚本任务
    public function addDownListExcel($param){
        //记录搜索条件脚本会执行,为了生成Excel准备的数据
        $condition = [
            'userId' => $param['user_ids']
        ];
        if(in_array($param['type'],[1,3,4,5])){
            $condition['startTime'] = empty($param['start_time']) ? '' : strtotime($param['start_time']);
            $condition['endTime'] = empty($param['end_time']) ? '' : strtotime($param['end_time']);
            $condition['gift_ids'] = isset($param['gift_ids']) ? $param['gift_ids'] : '';
            $condition['is_bag'] = isset($param['is_bag']) ? $param['is_bag'] : '';
            $condition['from_user_ids'] = isset($param['from_user_ids']) ? $param['from_user_ids'] : '';
            $condition['to_user_ids'] = isset($param['to_user_ids']) ? $param['to_user_ids'] : '';
        }
        $insert = [
            'exec_url'=>'',
            'condition' => json_encode($condition),
            'status' => 0,
            'type' => $param['type'],
            'created_at' => Carbon::now()->toDateTimeString()
        ];
        //查询是否存在
        $check = DB::table($this->excelOutTable)->where(['exec_url'=>'','condition'=>json_encode($condition),'status'=>0,'type'=>1])->exists();
        if(!$check){
            DB::table($this->excelOutTable)->insert($insert);  
        }
        return true;
    }

    //数据统计
    public function checkoutCount($startTime, $endTime, $goodsIds, $isBag=0, $toUserids='', $sendUserIds=''){
        $where = function ($query) use ($startTime, $endTime, $goodsIds, $isBag, $toUserids, $sendUserIds ) {
            if(!empty($startTime)){
                $query->where('change_timestamp','>=',strtotime($startTime)); 
            }
            if(!empty($endTime)){
                $query->where('change_timestamp','<=',strtotime($endTime)); 
            }
            if(!empty($goodsIds)){
                $goodsIds = explode(',',$goodsIds);
                $query->whereIn('gift_id',$goodsIds);
            }
            if($isBag != 0){
                $query->where('type',$isBag);
            }
            if(!empty($toUserids)){
                $toUserids = explode(',',$toUserids);
                $query->whereIn('user_id',$toUserids);
            }
            if(!empty($sendUserIds)){
                $sendUserIds = explode(',',$sendUserIds);
                $query->whereIn('from_user_id',$sendUserIds);
            }
        };
        $count = DB::connection($this->connection)->table($this->giftStream)->where($where)->groupBy('gift_id')->count();
        return $count;
    }

    //下载数据Excel列表(type类型 1=全家总动员 2=kov)
    public function downListExcel($type, $limit = 10, $page = 1){
        $where = function ($query) use ($type) {
            if(!empty($type)){
                $query->where('type',$type); 
            }
        };

        //获取数据
        $datas = DB::table($this->excelOutTable)
                ->where($where)
                ->orderBy('created_at','desc')
                ->paginate($limit,['*'],'page',$page);
        $datas = $this->objToArray($datas);
        $newdata = [];
        if(!empty($datas['data'])){
            foreach($datas['data'] as $k=>$v){
                $conditionArr = json_decode($v['condition'],true);
                if(in_array($v['type'],[3,4])){
                    $conditionStr = '礼物id:';
                    $conditionStr .= empty($conditionArr['gift_ids']) ? '全部' : $conditionArr['gift_ids'];
                    $conditionStr .= ' | 送礼人id:';
                    $conditionStr .= isset($conditionArr['from_user_ids']) ? '/' : $conditionArr['from_user_ids'];
                    $conditionStr .= ' | 收礼人id:';
                    $conditionStr .= isset($conditionArr['to_user_ids']) ? '/' : $conditionArr['to_user_ids'];
                    $conditionStr .= ' | 是否背包礼物:';
                    $is_bagStr = '/';
                    if(isset($conditionArr['is_bag'])){
                        if($conditionArr['is_bag'] == 1){
                            $is_bagStr = '否';
                        }elseif($conditionArr['is_bag'] == 2){
                            $is_bagStr = '是';
                        }
                    }
                    $conditionStr .= $is_bagStr;
                }else{
                    $conditionStr  = '用户id:';
                    $conditionStr .= empty($conditionArr['userId']) ? '全部' : $conditionArr['userId'];
                }
                
                $conditionStr .= ' | 开始时间:';
                $conditionStr .= empty($conditionArr['startTime']) ? '/' : date('Y-m-d',$conditionArr['startTime']);
                $conditionStr .= ' | 结束时间:';
                $conditionStr .= empty($conditionArr['endTime']) ? '/' : date('Y-m-d',$conditionArr['endTime']);

                $newdata[$k]['id'] = $v['id'];
                $newdata[$k]['exec_url'] = env('OSS_URL').$v['exec_url']; //下载URL
                $newdata[$k]['create_time'] = $v['created_at'];//生成时间
                $newdata[$k]['condition'] = $conditionStr;//搜索条件
                $newdata[$k]['type'] = $v['type'];//1=全家总动员 2=kov 3=礼物销量 4=送礼详情
                $newdata[$k]['status'] = $v['status'];//1=已生成 0=生成中 2=生成失败
            }
        }
        $datas['data'] = $newdata;
        return $datas;
    }
}
